<CFTRY>
	<CFIF ISDEFINED("url.tmpIdDet")>
		<CFSET tmpIdDet = "#url.tmpIdDet#">
		<CFSET client.common_path="../../common/">
		<CFSET GetEntry.SUBCOMPANYCODE="#URL.SUBCOMPCODE#">
	<CFELSE>
		<CFSET tmpIdDet = "#GetEntry.DOCNUMBER#">
	</CFIF>
	<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#DATEFORMAT(NOW(),'mm/dd/yyyy')#">
	<CFSET sDate = dateResult>
	<CFQUERY NAME="getSubcompany" DATASOURCE="#client.global_dsn#">
		SELECT A.IBOSEDSN
		FROM GRGDATALOCATION A, GRGSUBCOMPANY B
		WHERE B.SUBCOMPANYCODE='#GetEntry.SUBCOMPANYCODE#' AND A.DATALOCATIONCODE=B.DATALOCATIONCODE
	</CFQUERY>
	<CFSET theSubCompany="#getSubcompany.IBOSEDSN#">
	<CFQUERY NAME="getEmployees" DATASOURCE="#theSubCompany#">
		SELECT * FROM SINMOVEMENTEMPS
		WHERE DOCNUMBER = '#tmpIdDet#'
		ORDER BY PERSONNELIDNO
	</CFQUERY>
	<CFQUERY NAME="getEffectDate" DATASOURCE="#theSubCompany#">
		SELECT EFFECTIVEDATE,REASONCODE,ROUTEID FROM SINMOVEMENTFORMS
		WHERE DOCNUMBER = '#tmpIdDet#' AND EFFECTIVEDATE <= #preserveSingleQuotes(sDate)#
	</CFQUERY>
	<CFIF ISDEFINED("url.tmpIdDet")>
		<CFQUERY NAME="getApprover" DATASOURCE="#client.company_dsn#">
			SELECT ROUTER FROM CRGROUTEDETAILS
			WHERE ROUTEID = '#getEffectDate.ROUTEID#' AND STATUS='APPROVED'
			ORDER BY ORDERID
		</CFQUERY>
		<CFLOOP QUERY="getApprover">
			<CFSET CLIENT.CHAPA="#getApprover.ROUTER#">
		</CFLOOP>
		<CFQUERY NAME="GetGUID" DATASOURCE="#client.global_dsn#">
			SELECT GUID
			FROM  EGRGUSERMASTER
			WHERE USERID='#CLIENT.CHAPA#'
		</CFQUERY>
		<CFQUERY NAME="GetPID" DATASOURCE="#client.company_dsn#">
			SELECT PERSONNELIDNO
			FROM  CMFPA
			WHERE GUID='#GetGUID.GUID#'
		</CFQUERY>
		<CFSET CLIENT.CHAPA="#GetPID.PERSONNELIDNO#">
	</CFIF>
	<CFQUERY NAME="updateAPPROVEDPOST" DATASOURCE="#theSubCompany#">
		UPDATE SINMOVEMENTFORMS
		SET APPROVED = 'Y',
		APPROVEDBY = '#client.chapa#',
		DATEAPPROVED = #preserveSingleQuotes(sDate)#,
		POSTED = 'Y',
		POSTEDBY = '#client.chapa#',
		DATEPOSTED = #preserveSingleQuotes(sDate)#
		WHERE DOCNUMBER = '#tmpIdDet#'
	</CFQUERY>
	<CFIF getEffectDate.RECORDCOUNT>
		<CFIF ISDEFINED("url.tmpIdDet")>
			<CFSCHEDULE ACTION="Delete" TASK="Movement Schedule for #tmpIdDet# in database #theSubCompany#">
		</CFIF>
		<CFQUERY NAME="QueryViewAll" DATASOURCE="#theSubCompany#">
			SELECT * FROM SINMOVEMENTDELTA
			WHERE DOCNUMBER='#tmpIdDet#'
		</CFQUERY>
		<CFIF QueryViewAll.RECORDCOUNT>
			<CFLOOP QUERY="QueryViewAll">
				<CFQUERY NAME="UPDATEALL" DATASOURCE="#client.company_dsn#">
					UPDATE CMFPA
					SET #ITEMCODE#='#ITEMNEWVALUE#'
					WHERE PERSONNELIDNO='#PERSONNELIDNO#'
				</CFQUERY>
			</CFLOOP>
		</CFIF>
		<CFQUERY NAME="QueryViewCOSTCENTER" DATASOURCE="#theSubCompany#">
			SELECT * FROM SINMOVEMENTDELTA
			WHERE DOCNUMBER='#tmpIdDet#' AND ITEMCODE='COSTCENTERCODE'
		</CFQUERY>
		<CFIF QueryViewCOSTCENTER.RECORDCOUNT>
			<CFOUTPUT QUERY="QueryViewCOSTCENTER">
				<CFQUERY NAME="UPDATECOSTCENTER" DATASOURCE="#client.company_dsn#">
					UPDATE CMFPA
					SET COSTCENTERCODE='#ITEMNEWVALUE#'
					WHERE PERSONNELIDNO='#PERSONNELIDNO#'
				</CFQUERY>
			</CFOUTPUT>
		</CFIF>

		<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#getEffectDate.EFFECTIVEDATE#">
		<CFSET sEffctvdate = dateResult>
		<CFIF DATEFORMAT(NOW()) GTE DATEFORMAT(getEffectDate.EFFECTIVEDATE)>
			<CFSET PostOtherItems = true>
		<CFELSE>
			<CFSET PostOtherItems = false>
		</CFIF>
		<CFOUTPUT QUERY="getEmployees">
			<CFSET currID = PERSONNELIDNO>
			<CFQUERY NAME="getGuid" DATASOURCE="#client.company_dsn#">
				SELECT GUID FROM CMFPA WHERE PERSONNELIDNO = '#currID#'
			</CFQUERY>

			<CFQUERY NAME="getItemPerCandidate" DATASOURCE="#theSubCompany#">
				SELECT * FROM SINMOVEMENTDELTA
				WHERE DOCNUMBER = '#tmpIdDet#' AND PERSONNELIDNO = '#currID#'
					AND EFFECTIVEDATE <= #preserveSingleQuotes(sDate)#
				ORDER BY ITEMCODE
			</CFQUERY>
			<CFQUERY NAME="getPayrollID" DATASOURCE="#theSubCompany#">
				SELECT MOVEMENTID FROM SINMOVEMENTFORMS
				WHERE DOCNUMBER = '#tmpIdDet#'
			</CFQUERY>
			<CFQUERY NAME="getEntry" DATASOURCE="#client.company_dsn#">
				SELECT * FROM ECRGPAYROLL
				WHERE PAYROLLID = '#getPayrollID.MOVEMENTID#'
			</CFQUERY>
			<CFLOOP QUERY="getItemPerCandidate">
				<CFIF getItemPerCandidate.ITEMORIGINALVALUE NEQ getItemPerCandidate.ITEMNEWVALUE>
					<CFMODULE TEMPLATE="#client.common_path#cfm_moveitems.cfm"
						primaryKey="PERSONNELIDNO"
						pkVALUE="#currID#"
						itemCode="#getItemPerCandidate.ITEMCODE#"
						itemValue="#getItemPerCandidate.ITEMNEWVALUE#"
						POSTTABLE="SINMOVEMENTDELTA"
						POSTPRIMARY = "DOCNUMBER,ITEMCODE,PERSONNELIDNO"
						POSTPRIMARYVAL="#tmpIdDet#,#getItemPerCandidate.ITEMCODE#,#currID#"
						POSTDSN="#theSubCompany#"
						REASONCODE="#getItemPerCandidate.REASONCODE#"
						MONTHLY="#getItemPerCandidate.MONTHLY#"
						WITHRETRO="#getItemPerCandidate.WITHRETRO#"
						RETROSTARTDATE="#getItemPerCandidate.RETROSTARTDATE#"
						RETROENDDATE="#getItemPerCandidate.RETROENDDATE#"
						DOCNUMBER="#tmpIdDet#"
						EFFECTIVEDATE="#getItemPerCandidate.EFFECTIVEDATE#"
						GLOBAL_DSN = "#client.global_dsn#"
						COMPANY_DSN = "#client.company_dsn#"
						SUBCOMPANY_DSN = "#theSubCompany#"
						DBMS = "#client.dbms#">

					<CFMODULE TEMPLATE="#client.common_path#add_link_items.cfm"
						ITEMCODE = "#getItemPerCandidate.ITEMCODE#"
						PERSONID = "#currID#"
						EFFECTIVEDATE = "#getItemPerCandidate.EFFECTIVEDATE#"
						GLOBAL_DSN = "#client.global_dsn#"
						COMPANY_DSN = "#client.company_dsn#"
						SUBCOMPANY_DSN = "#theSubCompany#"
						DBMS = "#client.dbms#"
						NEWVALUE="#getItemPerCandidate.ITEMNEWVALUE#">

					<CFMODULE TEMPLATE="#client.common_path#cfm_movehist.cfm"
						ITEMCODE = "#getItemPerCandidate.ITEMCODE#"
						ORIGINALVALUE = "#getItemPerCandidate.ITEMORIGINALVALUE#"
						NEWVALUE = "#getItemPerCandidate.ITEMNEWVALUE#"
						EFFECTIVEDATE = "#getItemPerCandidate.EFFECTIVEDATE#"
						REASONCODE = "#getItemPerCandidate.REASONCODE#"
						ORIGINALDESC = "null"
						NEWDESC = "null"
						GUID = "#getGuid.GUID#"
						PERSONNELIDNO = "#currID#"
						GLOBAL_DSN = "#client.global_dsn#"
						COMPANY_DSN = "#client.company_dsn#"
						SUBCOMPANY_DSN = "#theSubCompany#"
						DBMS = "#client.dbms#"
						>
				</CFIF>
			</CFLOOP>
			<CFIF PostOtherItems >
				<CFMODULE TEMPLATE="#client.common_path#post_movement_data.cfm"
					common_path="#client.common_path#"
					company_dsn="#client.company_dsn#"
					subcompany_dsn="#theSubCompany#"
					docnumber="#tmpIdDet#"
					personnelidno="#currID#"
					guid="#getGuid.GUID#"
					REASONCODE="#getEffectDate.REASONCODE#"
					EFFECTIVITYDATE="#sEffctvdate#">
			</CFIF>

			<CFMODULE TEMPLATE="#client.common_path#cfm_movecasfilterfields.cfm"
				primaryKey="PERSONNELIDNO"
				pkVALUE="#currID#"
				itemCodes="#quotedValueList(getItemPerCandidate.ITEMCODE)#"
				DBMS = "#client.dbms#"
				GLOBAL_DSN = "#client.global_dsn#">
		</CFOUTPUT>

		<CFIF PostOtherItems>
			<CFQUERY NAME="qryUpdatePOSTITEMS" DATASOURCE="#theSubCompany#">
				UPDATE SINMOVEMENTFORMS
				SET
				POSTITEMS = 'Y'
				WHERE DOCNUMBER = '#tmpIdDet#'
			</CFQUERY>
		</CFIF>
		<CFQUERY NAME="qrySINMOVEMENTFORMS" DATASOURCE="#theSubCompany#">
			SELECT INTERCOMPANY FROM SINMOVEMENTFORMS
			WHERE DOCNUMBER = '#tmpIdDet#' AND INTERCOMPANY='Y'
		</CFQUERY>
		<CFIF qrySINMOVEMENTFORMS.RECORDCOUNT>
			<CFIF client.DBMS EQ "ORACLE">
				<CFSET sDate = "to_date('#DATEFORMAT(NOW(), "mm/dd/yyyy")#','mm/dd/yyyy')">
			<CFELSE>
				<CFSET sDate = "'#DATEFORMAT(NOW(), "mm/dd/yyyy")#'">
			</CFIF>
			<CFQUERY NAME="qryEffectivityDate" DATASOURCE="#theSubCompany#">
				SELECT
				EFFECTIVEDATE,REASONCODE
				FROM
				SINMOVEMENTFORMS WHERE DOCNUMBER = '#tmpIdDet#' AND
				EFFECTIVEDATE <= #preserveSingleQuotes(sDate)#
			</CFQUERY>
			<!------>
			<CFIF qryEffectivityDate.RecordCount NEQ 0>

				<CFQUERY NAME="qrySINMOVEMENTDATA" DATASOURCE="#theSubCompany#">
					SELECT
					TABLENAME, LEVELID
					FROM SINMOVEMENTDATA
					WHERE DOCNUMBER = '#tmpIdDet#'
				</CFQUERY>
				<CFQUERY NAME="qrySINMOVEMENTEMPS" DATASOURCE="#theSubCompany#">
					SELECT PERSONNELIDNO,IDBEFOREPOST,ONDUPLICATEID
					FROM SINMOVEMENTEMPS
					WHERE DOCNUMBER='#tmpIdDet#'
				</CFQUERY>
				<CFQUERY NAME="qrySININTERCOMSETUP" DATASOURCE="#theSubCompany#">
					SELECT * FROM SININTERCOMSETUP WHERE DOCNUMBER = '#tmpIdDet#'
				</CFQUERY>
				<CFSET OnDulplicate = "#qrySININTERCOMSETUP.ONDUPLICATEACTION#">

				<CFLOOP QUERY="qrySINMOVEMENTDATA">
					<CFSET sTableName = "#qrySINMOVEMENTDATA.TABLENAME#">
					<CFLOOP QUERY="qrySINMOVEMENTEMPS">
						<CFQUERY NAME="qrySINMOVEMENTCOMPANY" DATASOURCE="#theSubCompany#">
							SELECT SUBCOMPANYCODE, COMPANYCODE
							FROM SINMOVEMENTCOMPANY
							WHERE PERSONNELIDNO = '#qrySINMOVEMENTEMPS.PERSONNELIDNO#' AND DOCNUMBER='#tmpIdDet#'
						</CFQUERY>
						<CFSET sLvl = "#qrySINMOVEMENTDATA.LEVELID#">
						<CFSET sSubcompanyCode = "#qrySINMOVEMENTCOMPANY.SUBCOMPANYCODE#">
						<CFSET sCompanyCode = "#qrySINMOVEMENTCOMPANY.COMPANYCODE#">
						<!---	SET EMPLOYEMENT STATUS TO SEPARATED --->
						<CFIF qrySININTERCOMSETUP.SETSEPARATED EQ "Y">
							<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#qrySININTERCOMSETUP.STATUSENDDATE#">
							<CFSET sDate2 = dateResult>
							<CFQUERY NAME="qryCMFPA" DATASOURCE="#client.company_dsn#">
								UPDATE CMFPA SET EMPLOYMENTSTATUS = 'SEPARATED',  STATUSENDDATE = #preserveSingleQuotes(sDate2)#
								WHERE PERSONNELIDNO = '#qrySINMOVEMENTEMPS.PERSONNELIDNO#'
							</CFQUERY>
							<CFIF ISDEFINED("GETENTRY.NOTIFYEMAILTEMPLATE") AND GETENTRY.NOTIFYEMAILTEMPLATE NEQ "" AND GETENTRY.NOTIFYEMAILTEMPLATE NEQ "|NULL|">
								<CFINCLUDE TEMPLATE="../../webshell/mailcreator/templates/movementnotification.cfm">
							</CFIF>
						</CFIF>
						<!---end--->
						<CFIF qrySININTERCOMSETUP.KEEPRECORD EQ "Y">
							<CFSET sPersonnelID = "#qrySINMOVEMENTEMPS.PERSONNELIDNO#">
						<CFELSE>
							<CFSET sPersonnelID =  "#qrySINMOVEMENTEMPS.IDBEFOREPOST#">
						</CFIF>
						<CFIF qrySININTERCOMSETUP.RETAINRECORD EQ "N" AND NOT sTableName EQ "CMFPA">
							<CFQUERY NAME="qryDeleteEmployeeRecord" DATASOURCE="#sDsn_Src#">
								DELETE FROM #sTableName# WHERE PERSONNELIDNO = '#qrySINMOVEMENTEMPS.PERSONNELIDNO#'
							</CFQUERY>
						</CFIF>
						<CFQUERY NAME="qryGetSubcompany" DATASOURCE="#client.company_dsn#">
							SELECT SUBCOMPANYCODE FROM CMFPA WHERE PERSONNELIDNO = '#qrySINMOVEMENTEMPS.PERSONNELIDNO#'
						</CFQUERY>
						<CFQUERY NAME="qryUpdateSubcompany" DATASOURCE="#client.company_dsn#">
							UPDATE CMFPA
							SET PERSONNELIDNO = '#sPersonnelID#',
							SUBCOMPANYCODE = '#qrySINMOVEMENTCOMPANY.SUBCOMPANYCODE#'
							WHERE PERSONNELIDNO = '#qrySINMOVEMENTEMPS.PERSONNELIDNO#'
						</CFQUERY>

						<CFQUERY NAME="qryGetSubcompanyDSN" DATASOURCE="#client.global_dsn#">
							SELECT A.IBOSEDSN
							FROM GRGDATALOCATION A,
							GRGSUBCOMPANY B
							WHERE A.DATALOCATIONCODE = B.DATALOCATIONCODE AND B.SUBCOMPANYCODE = '#sSubcompanyCode#'
							AND B.COMPANYCODE='#sCompanyCode#'
						</CFQUERY>
						<CFQUERY NAME="qryGetCompanyDSN" DATASOURCE="#client.global_dsn#">
							SELECT A.IBOSEDSN
							FROM GRGDATALOCATION A,
							GRGCOMPANY B
							WHERE A.DATALOCATIONCODE = B.DATALOCATIONCODE
							AND B.COMPANYCODE='#sCompanyCode#'
						</CFQUERY>

						<CFSET sSubcompany_Dsn = "#qryGetSubcompanyDSN.IBOSEDSN#">
						<CFSET sCompany_Dsn = "#qryGetCompanyDSN.IBOSEDSN#">
						<CFSET sDsn_Src = "">
						<CFSET sDsn_Dst = "">
						<CFSWITCH expression="#sLvl#">
							<CFCASE VALUE="C">
								<CFSET sDsn_Src = "#client.company_dsn#">
								<CFSET sDsn_Dst = "#sCompany_Dsn#">
							</CFCASE>
							<CFCASE VALUE="S">
								<CFSET sDsn_Src = "#theSubCompany#">
								<CFSET sDsn_Dst = "#sSubcompany_Dsn#">
							</CFCASE>
						</CFSWITCH>

						<CFIF sTableName EQ "SINMOVEMENTFORMS">
							<CFQUERY NAME="qrySource" DATASOURCE="#sDsn_Src#">
								SELECT * FROM #sTableName# WHERE DOCNUMBER ='#tmpIdDet#'
							</CFQUERY>
						<CFELSE>
							<CFQUERY NAME="qrySource" DATASOURCE="#sDsn_Src#">
								SELECT * FROM #sTableName# WHERE PERSONNELIDNO = '#sPersonnelID#'
							</CFQUERY>
						</CFIF>

						<CFSET theColumns="">
						<CFQUERY NAME="qryDataType" DATASOURCE="#client.global_dsn#">
							SELECT COLUMNNAME, COLUMNTYPE
							FROM GRGBOSETABLEFIELDS
							WHERE (TABLENAME = '#sTableName#') AND
							(
							COLUMNNAME IN
							(
							<CFSET nLoop=1>
							<CFLOOP LIST="#qrySource.ColumnList#" INDEX="x">
								<CFIF nLoop NEQ 1>,</CFIF>'#x#'
								<CFSET theColumns=listAppend(theColumns, #x#)>
								<CFSET nLoop=nLoop + 1>
							</CFLOOP>
							)
							)
						</CFQUERY>
						<CFSET lSkip = true>
						<CFIF OnDulplicate EQ "REPLACE">
							<CFQUERY NAME="qryDeleteDuplicate" DATASOURCE="#sDsn_Dst#">
								DELETE FROM #sTableName# WHERE PERSONNELIDNO = '#sPersonnelID#'
							</CFQUERY>
						<CFELSEIF OnDulplicate EQ "SKIP">
							<CFIF sTableName EQ "SINMOVEMENTFORMS">
								<CFQUERY NAME="qryIsDuplicate" DATASOURCE="#sDsn_Dst#">
									SELECT DOCNUMBER FROM #sTableName# WHERE DOCNUMBER ='#tmpIdDet#'
								</CFQUERY>
							<CFELSE>
								<CFQUERY NAME="qryIsDuplicate" DATASOURCE="#sDsn_Dst#">
									SELECT PERSONNELIDNO FROM #sTableName# WHERE PERSONNELIDNO = '#sPersonnelID#'
								</CFQUERY>
							</CFIF>
							<CFSET lSkip = IIF(qryIsDuplicate.Recordcount GT 0, TRUE,TRUE)>
						<CFELSEIF OnDulplicate EQ "ISSUEDNEW">
							<CFQUERY NAME="qryIsDuplicate" DATASOURCE="#sDsn_Dst#">
								<!---SELECT PERSONNELIDNO FROM #sTableName# WHERE PERSONNELIDNO = '#sPersonnelID#'--->
								SELECT PERSONNELIDNO FROM CMFPA WHERE PERSONNELIDNO = '#sPersonnelID#'
							</CFQUERY>
							<CFIF qryIsDuplicate.Recordcount GT 0>
								<CFSET sPersonnelID = "#qrySINMOVEMENTEMPS.ONDUPLICATEID#">
							</CFIF>
						</CFIF>
						<CFLOOP QUERY="qrySource">

						</CFLOOP>

						<CFIF lSkip>
							<CFLOOP QUERY="qrySource">
								<CFSET sValueList = "">
								<CFSET sV = "">
								<CFLOOP LIST="#qrySource.ColumnList#" INDEX="x">
									<CFSET sVal = evaluate("qrySource.#x#")>
									<CFIF sVal EQ "">
										<CFSET sVal = "NULL">
									</CFIF>
									<CFIF sV EQ "">
										<CFIF sVal EQ "NULL">
											<CFSET sV ="#sVal#">
										<CFELSE>
											<CFSET sV ="'#sVal#'">
										</CFIF>
									<CFELSE>
										<CFIF sVal EQ "NULL">
											<CFSET sV = sv & ",#sVal#">
										<CFELSE>
											<CFSET sV = sv & ",'#sVal#'">
										</CFIF>
									</CFIF>
									<CFSET sValueList = sValueList & "@" & x & "@|||" & sV & "~">
								</CFLOOP>

								<CFQUERY NAME="qryInsert" DATASOURCE="#sDsn_Dst#">
										INSERT INTO #sTableName#
										(#theColumns#)

										VALUES
										(#preserveSingleQuotes(sV)#)
								</CFQUERY>
							</CFLOOP>
						</CFIF>
					</CFLOOP>
				</CFLOOP>
			</CFIF>
			<CFQUERY NAME="qryInsertHistory" DATASOURCE="#client.company_dsn#">
				INSERT INTO CINMOVEMENTHIST(PERSONNELIDNO,ITEMCODE,ORIGINALVALUE,NEWVALUE,EFFECTIVEDATE,REASONCODE, GUID,RECCREATEDBY, RECDATECREATED, USERID, DATELASTUPDATE, TIMELASTUPDATE)
				VALUES('#qrySINMOVEMENTEMPS.PERSONNELIDNO#','COMPANYMOVEMENT','#qryGetSubcompany.SUBCOMPANYCODE#','#sSubcompanyCode#',#preserveSingleQuotes(sDate)#,'#qryEffectivityDate.REASONCODE#')
			</CFQUERY>
		</CFIF>
	<CFELSE>
		<CFQUERY NAME="getEffectDate" DATASOURCE="#theSubCompany#">
			SELECT EFFECTIVEDATE,REASONCODE FROM SINMOVEMENTFORMS
			WHERE DOCNUMBER = '#tmpIdDet#'
		</CFQUERY>
		<CFSCHEDULE ACTION="Update"
		    TASK="Movement Schedule for #tmpIdDet# in database #theSubCompany#"
		    OPERATION="HTTPRequest"
		    STARTDATE="#getEffectDate.EFFECTIVEDATE#"
		    STARTTIME="12:01 AM"
		    INTERVAL="once"
		    URL="#client.domain#eforms/post_activities/postMovements.cfm?tmpIdDet=#tmpIdDet#&SUBCOMPCODE=#GetEntry.SUBCOMPANYCODE#">
`	</CFIF>

	<CFCATCH TYPE="ANY">
		<CFSET sWarn = #CFCATCH.message# & #CFCATCH.DETAIL#>
	</CFCATCH>
</CFTRY>
